#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
差旅费用数据分析脚本

主要功能：
1. 按单据编号和人员编号分组，并按出发时间和辅助编号排序
2. 设置每组第一条记录的出发地名称为该组的常住地
3. 筛选交通工具编号/交通工具为"6002"的数据
4. 计算出差天数：
   - 将出发地和目的地都等于常住地的记录分为一组
   - 计算到达时间和出发时间的差值作为出差天数
"""

import pandas as pd
from pathlib import Path
from datetime import datetime

# 配置常量
DATA_DIR = Path("data")
OUTPUT_DIR = DATA_DIR / "alldata"  # 输出目录

# 输入文件
TRAVEL_DETAILS_FILE = DATA_DIR / "差旅报销行程明细(商旅).xlsx"
TRANSPORT_EXPENSE_FILE = DATA_DIR / "差旅费城市间交通费.xlsx"
TRAVEL_ALLOWANCE_FILE = DATA_DIR / "差旅费出差补助.xlsx"

# 输出文件
OUTPUT_FILE = OUTPUT_DIR / "自带车报销交通补助分析结果.xlsx"

def clean_column_names(df):
    """
    清理DataFrame的列名，去除多余的空格
    """
    return df.rename(columns=lambda x: x.strip())

def load_data():
    """
    加载Excel文件的数据并进行基础处理
    
    返回值：
        tuple: 包含DataFrame的元组
    """
    # 确保输出目录存在
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    
    try:
        # 读取Excel文件
        travel_details = pd.read_excel(TRAVEL_DETAILS_FILE,dtype='object')
        transport_expenses = pd.read_excel(TRANSPORT_EXPENSE_FILE)
        travel_allowance = pd.read_excel(TRAVEL_ALLOWANCE_FILE)  # 添加读取差旅费出差补助表
        
        # 清理列名中的空格
        travel_details = clean_column_names(travel_details)
        transport_expenses = clean_column_names(transport_expenses)
        travel_allowance = clean_column_names(travel_allowance)
        
        # 打印列名，帮助调试
        print("\n第一个表的列名：")
        print(travel_details.columns.tolist())
        print("\n第二个表的列名：")
        print(transport_expenses.columns.tolist())
        print("\n第三个表的列名：")
        print(travel_allowance.columns.tolist())
        
        # 将日期时间列转换为统一格式
        datetime_columns = ['出发时间', '到达时间']
        
        for df in [travel_details, transport_expenses]:
            for col in datetime_columns:
                if col in df.columns:
                    df[col] = pd.to_datetime(df[col], errors='coerce')
        
        # 将分录编号和辅助编号转换为4位字符串格式
        for df in [travel_details, transport_expenses]:
            if '分录编号' in df.columns:
                df['分录编号'] = df['分录编号'].astype(str).str.zfill(4)
            if '辅助编号' in df.columns:
                df['辅助编号'] = df['辅助编号'].astype(str).str.zfill(4)
        
        return travel_details, transport_expenses, travel_allowance
    
    except Exception as e:
        print(f"加载数据时出错: {str(e)}")
        raise

def analyze_travel_data(travel_details, transport_expenses, travel_allowance):
    """
    分析差旅数据
    
    参数：
        travel_details: 差旅报销行程明细
        transport_expenses: 差旅费城市间交通费
        travel_allowance: 差旅费出差补助
        
    返回值：
        DataFrame: 分析结果
    """
    try:
        # 重命名第二个表的列名以匹配第一个表
        column_rename = {
            '员工编号': '人员编号',
            '起始地名称': '出发地名称',
            '交通工具': '交通工具编号'
        }
        transport_expenses = transport_expenses.rename(columns=column_rename)
        # 合并数据
        merged_data = pd.merge(
            travel_details,
            transport_expenses[['单据编号', '人员编号', '出发地名称', '出发时间', '到达时间', '交通工具编号']],
            on=['单据编号', '人员编号', '出发地名称', '出发时间'],
            how='left',
            suffixes=('', '_transport')
        )
        # 处理到达时间
        # if '到达时间' in merged_data.columns:
        #     # 先用第二个表的到达时间填充
        #     merged_data['到达时间'] = merged_data['到达时间_transport']
        #     # 对于仍然为空的值，使用第二个表的出发时间
        #     mask = merged_data['到达时间'].isna()
        #     merged_data.loc[mask, '到达时间'] = merged_data.loc[mask, '出发时间']
        
        # 按单据编号和人员编号分组，并按出发时间和辅助编号排序
        # merged_data.sort_values(
        #     by=['单据编号', '人员编号', '出发时间', '辅助编号'],
        #     ascending=[True, True, True, True],
        #     inplace=True
        # )

        merged_data.sort_values(
            by=['单据编号', '人员编号'],
            ascending=[True, True],
            inplace=True
        )
        # 计算出差天数
        def calculate_travel_days(group):
            # 初始化出差天数列和常住地列
            group['出差天数'] = None
            group['常住地'] = None
            group['优先级'] = None
            
            # 获取组内的所有索引
            group_indices = group.index.tolist()
            
            if len(group_indices) == 0:
                return group
                
            # 设置初始常住地（第一条记录的出发地名称）
            initial_residence = group.loc[group_indices[0], '出发地名称']
            
            i = 0
            while i < len(group_indices):
                if i == 0:
                    # 第一段行程使用初始常住地
                    current_residence = initial_residence
                else:
                    # 后续段使用当前记录的出发地名称作为新的常住地
                    current_residence = group.loc[group_indices[i], '出发地名称']
                
                # 记录当前段的起始时间
                start_time = group.loc[group_indices[i], '出发时间']
                
                # 设置这条记录的常住地
                group.loc[group_indices[i:], '常住地'] = current_residence
                
                # 在当前位置之后查找第一条目的地等于常住地的记录
                found = False
                for j in range(i, len(group_indices)):
                    if group.loc[group_indices[j], '目的地名称'] == current_residence:
                        end_time = group.loc[group_indices[j], '到达时间']
                        # 计算出差天数
                        days = (end_time - start_time).total_seconds() / (24 * 3600) + 1
                        # 为这一段的所有记录设置相同的出差天数
                        group.loc[group_indices[i:j+1], '出差天数'] = days
                        # 设置优先级
                        group.loc[group_indices[i:j+1], '优先级'] = '一级' if days <= 2 else '二级'
                        # 移动到下一个起点
                        i = j + 1
                        found = True
                        break
                
                if not found:
                    # 如果没有找到匹配的记录，移动到下一条
                    i += 1
            
            return group
        
        # 应用分组处理
        # merged_data = merged_data.groupby(['单据编号', '人员编号']).apply(calculate_travel_days)
        
        # 过滤出差天数为空或负数的数据
        # valid_data = merged_data[
        #     merged_data['出差天数'].notna() & (merged_data['出差天数'] > 0)
        # ].copy()
        
        # 确保交通工具编号为数值型并筛选6002
        merged_data['交通工具编号'] = pd.to_numeric(merged_data['交通工具编号'], errors='coerce')
        
        filtered_data = merged_data[merged_data['交通工具编号'] == 6002].copy()
        
        print(f"\n筛选出交通工具编号为6002的记录数: {len(filtered_data)}")
        
        if filtered_data.empty:
            print("警告：未找到交通工具编号为6002的数据")
            return pd.DataFrame()
        
        # 处理时间格式，只保留年月日
        datetime_columns = ['出发时间', '到达时间']
        for col in datetime_columns:
            if col in filtered_data.columns:
                filtered_data[col] = filtered_data[col].dt.strftime('%Y-%m-%d')
        
        # 获取交通补助金额
        traffic_allowance = travel_allowance[
            (travel_allowance['补助类型名称'] == '交通补助') & 
            (pd.to_numeric(travel_allowance['金额'], errors='coerce') > 0)
        ][['单据编号', '金额']].copy()

        # 将金额按单据编号合并到结果中
        filtered_data = pd.merge(
            filtered_data.reset_index(drop = True),
            traffic_allowance,
            on='单据编号',
            how='left'
        )
        
        # 将NaN值填充为0
        filtered_data['金额'] = filtered_data['金额'].fillna(0)
        
        # 重命名金额列为交通补助金额
        filtered_data = filtered_data.rename(columns={'金额': '交通补助金额'})
        
        # 删除多余的列
        columns_to_drop = [col for col in filtered_data.columns if col.endswith('_transport')]
        filtered_data = filtered_data.drop(columns=columns_to_drop)
        
        #删除重复值
        filtered_data = filtered_data.drop_duplicates()
        
        # 过滤掉交通补助金额为0的记录
        final_data = filtered_data[pd.to_numeric(filtered_data['交通补助金额'], errors='coerce') > 0].copy()
        
        print(f"\n最终记录数（过滤金额为0后）: {len(final_data)}")
        print(f"金额为0而被过滤的记录数: {len(filtered_data) - len(final_data)}")
        
        # 打印优先级统计
        # priority_counts = final_data['优先级'].value_counts()
        # print("\n优先级统计:")
        # print(f"一级（小于等于2天）: {priority_counts.get('一级', 0)}条")
        # print(f"二级（大于2天）: {priority_counts.get('二级', 0)}条")
        
        return final_data
        
    except Exception as e:
        print(f"分析数据时出错: {str(e)}")
        raise

def main(once = False):
    """主程序入口"""
    try:
        print("正在加载数据...")
        travel_details, transport_expenses, travel_allowance = load_data()
        
        if travel_details.empty or transport_expenses.empty:
            print("错误：输入数据为空")
            return False
            
        print("正在分析数据...")
        result = analyze_travel_data(travel_details, transport_expenses, travel_allowance)
        
        if result.empty:
            print("未找到匹配的数据")
            return False

        if once:
            return result

        # 保存结果
        with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl', mode='w') as writer:
            result.to_excel(writer, index=False)
            # 设置分录编号和辅助编号列为文本格式
            if any(col in result.columns for col in ['分录编号', '辅助编号']):
                worksheet = writer.sheets['Sheet1']
                for idx, col in enumerate(result.columns):
                    if col in ['分录编号', '辅助编号']:
                        # 设置整列为文本格式
                        col_letter = chr(65 + idx)  # 将列索引转换为Excel列字母
                        worksheet.column_dimensions[col_letter].number_format = '@'
                        # 设置每个单元格为文本格式
                        for row in range(2, len(result) + 2):  # Excel是1-based，第一行是标题
                            cell = worksheet.cell(row=row, column=idx + 1)
                            cell.number_format = '@'  # 设置单元格格式为文本
                            # 确保单元格的值是字符串格式
                            cell.value = str(cell.value).zfill(4)
        
        print(f"\n分析完成。结果已保存至: {OUTPUT_FILE}")
        print(f"处理的记录总数: {len(result)}")
        
        # 打印统计信息
        print("\n数据统计:")
        print(f"唯一单据编号数量: {result['单据编号'].nunique()}")
        print(f"唯一人员编号数量: {result['人员编号'].nunique()}")
        # print(f"包含出差天数的记录数: {result['出差天数'].notna().sum()}")
        print(f"包含交通补助的记录数: {(result['交通补助金额'] > 0).sum()}")
        
        return True
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        raise

if __name__ == "__main__":
    main() 